﻿using Dapper;
using System;
using BCEntity.Trading.Purchase.GoodsPurchase;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCCommon.TradingEnums;
using BCCommon.Extensions;

namespace BCData.Trading.Purchase.GoodsPurchase
{
    /// <summary>
    /// 商品采购单
    /// </summary>
    public class GoodsPurchaseData : IGoodsPurchaseData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM goodspurchase";
        private const string QUERY = @"SELECT GoodsPurchaseId,UpdateTime,EditAdminId,EditAdminName,HandleTime,HasNotifyPurchaseManage,DepositePaymentDate,LastPaymentDate,
                                            GoodsPurchaseNo,DepositePaymentCertificateNo,LastPaymentCertificateNo,PaidDepositeAmount,PaidLastAmount,LatestPaidLastAmount,DepositAmount,
                                            LastPaymentAmount,RecordAmount,SupportSoldPayment,CreateAdminId,CreateAdminName,CreateTime,SuspendRemark,SuspendTime,RestoreRemark,
                                            RestoreTime,StopRemark,StopTime,PurchaseStatus,PurchaseStatusStr,InventoryStatus,InventoryStatusStr,DeliveryDate,PurchaseContractNo,
                                            PurchaseContractRemark
                                        FROM goodspurchase";
        public GoodsPurchaseData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }
        #region 检查商品采购单是否存在
        public async Task<bool> Exists(long goodsPurchaseId)
        {
            string sql = string.Format(@"{0} WHERE GoodsPurchaseId=?GoodsPurchaseId LIMIT 1", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        public async Task<bool> Exists(string goodsPurchaseNo)
        {
            string sql = string.Format(@"{0} WHERE GoodsPurchaseNo=?GoodsPurchaseNo LIMIT 1", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseNo", goodsPurchaseNo, DbType.AnsiString);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 添加商品采购单
        public async Task<GoodsPurchaseEntity> Add(GoodsPurchaseEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("goodspurchase", new string[] { "GoodsPurchaseNo", "DepositAmount", "LastPaymentAmount", "RecordAmount", "SupportSoldPayment",
                "CreateAdminId", "CreateAdminName","PurchaseStatus","PurchaseStatusStr" });
            sql = string.Format("{0} {1} WHERE GoodsPurchaseId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseNo", entity.GoodsPurchaseNo?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?DepositAmount", entity.DepositAmount, DbType.Decimal, size: 16);
            parameters.Add("?LastPaymentAmount", entity.LastPaymentAmount, DbType.Decimal, size: 16);
            parameters.Add("?RecordAmount", entity.RecordAmount, DbType.Decimal, size: 16);
            parameters.Add("?SupportSoldPayment", entity.SupportSoldPayment, DbType.Boolean);
            parameters.Add("?CreateAdminId", entity.CreateAdminId, DbType.Int64, size: 20);
            parameters.Add("?CreateAdminName", entity.CreateAdminName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?PurchaseStatus", PurchaseStatusType.Ready.ToString(), DbType.AnsiString, size: 30);
            parameters.Add("?PurchaseStatusStr", PurchaseStatusType.Ready.GetDisplayString(), DbType.AnsiString, size: 30);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<GoodsPurchaseEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改商品采购单
        public async Task<GoodsPurchaseEntity> Update(GoodsPurchaseEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("goodspurchase", new string[] { "GoodsPurchaseNo", "DepositAmount", "LastPaymentAmount", "RecordAmount", "SupportSoldPayment",
                "EditAdminId", "EditAdminName" }, new string[] { "GoodsPurchaseId" });
            sql = string.Format("{0} {1} WHERE GoodsPurchaseId=?GoodsPurchaseId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", entity.GoodsPurchaseId, DbType.Int64, size: 20);
            parameters.Add("?EditAdminId", entity.EditAdminId, DbType.Int64, size: 20);
            parameters.Add("?EditAdminName", entity.EditAdminName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?GoodsPurchaseNo", entity.GoodsPurchaseNo?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?DepositAmount", entity.DepositAmount, DbType.Decimal, size: 16);
            parameters.Add("?LastPaymentAmount", entity.LastPaymentAmount, DbType.Decimal, size: 16);
            parameters.Add("?RecordAmount", entity.RecordAmount, DbType.Decimal, size: 16);
            parameters.Add("?SupportSoldPayment", entity.SupportSoldPayment, DbType.Boolean);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<GoodsPurchaseEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 删除商品采购单
        public async Task<bool> Delete(long goodsPurchaseId)
        {
            string sql = @"DELETE FROM goodspurchase WHERE GoodsPurchaseId=?GoodsPurchaseId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 获取商品采购单
        public async Task<GoodsPurchaseEntity> GetEntity(long goodsPurchaseId)
        {
            string sql = string.Format(@"{0} WHERE GoodsPurchaseId=?GoodsPurchaseId LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<GoodsPurchaseEntity>(sql, parameters);
        }
        #endregion

        #region 获取商品采购单分页
        public async Task<(IEnumerable<GoodsPurchaseEntity>, int)> Query(string purchaseNo, PurchaseInventoryStatusType? purchaseInventoryStatusType, PurchaseDateType? purchaseDateType, DateTime? start, DateTime? end, string sortName, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(purchaseNo))
            {
                wheres.Add("GoodsPurchaseNo LIKE CONCAT('%',CONCAT(?GoodsPurchaseNo,'%'))");
                parameters.Add("?GoodsPurchaseNo", purchaseNo?.Trim(), DbType.AnsiString);
            }
            if (purchaseInventoryStatusType.HasValue)
            {
                wheres.Add("InventoryStatus=?InventoryStatus)");
                parameters.Add("?InventoryStatus", purchaseInventoryStatusType.Value.ToString(), DbType.AnsiString);
            }
            if (!purchaseDateType.HasValue)
            {
                purchaseDateType = PurchaseDateType.CreateTime;
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add(string.Format("{0}>=?Start", purchaseDateType.ToString()));
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add(string.Format("{0}<=?End", purchaseDateType.ToString()));
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add(string.Format("{0}>=?Start AND {0}<=?End", purchaseDateType.ToString()));
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }

            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; SELECT COUNT(1) FROM goodspurchase {1}",
                  QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            return await this.databaseContext.Slave.QueryMultipleAsync<GoodsPurchaseEntity, int>(sql, parameters);
        }
        #endregion

        #region 修改采购状态
        public async Task<bool> UpdatePurchaseStatus(long goodsPurchaseId, PurchaseStatusType purchaseStatusType)
        {
            string sql = SqlCommands.GetUpdateSql("goodspurchase", new string[] { "PurchaseStatus", "PurchaseStatusStr" }, new string[] { "GoodsPurchaseId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseId, DbType.Int64);
            parameters.Add("?PurchaseStatus", purchaseStatusType.ToString(), DbType.AnsiString,size:30);
            parameters.Add("?PurchaseStatusStr", purchaseStatusType.GetDisplayString(), DbType.AnsiString, size: 30);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion
        #region 修改采购定金支付信息
        public async Task<GoodsPurchaseEntity> UpdateDepositePaymentInfo(GoodsPurchaseEntity goodsPurchaseEntity)
        {
            string sql = SqlCommands.GetUpdateSql("goodspurchase", new string[] { "DepositePaymentDate", "PaidDepositeAmount",
                "PurchaseStatus", "PurchaseStatusStr","HandleTime", "InventoryStatus", "InventoryStatusStr" }, new string[] { "GoodsPurchaseId" });
            sql = string.Format("{0} {1} WHERE GoodsPurchaseId=?GoodsPurchaseId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseEntity.GoodsPurchaseId, DbType.Int64);
            parameters.Add("?DepositePaymentDate", goodsPurchaseEntity.DepositePaymentDate?.Date, DbType.DateTime);
            parameters.Add("?PaidDepositeAmount", goodsPurchaseEntity.PaidDepositeAmount, DbType.Decimal);
            parameters.Add("?HandleTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?PurchaseStatus", PurchaseStatusType.Running.ToString(), DbType.AnsiString, size: 30);
            parameters.Add("?PurchaseStatusStr", PurchaseStatusType.Running.GetDisplayString(), DbType.Int64);
            parameters.Add("?InventoryStatus", PurchaseInventoryStatusType.Pending.ToString(), DbType.AnsiString, size: 30);
            parameters.Add("?PurchaseStatusStr", PurchaseInventoryStatusType.Pending.GetDisplayString(), DbType.AnsiString, size: 30);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<GoodsPurchaseEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改采购尾款支付信息
        public async Task<GoodsPurchaseEntity> UpdateLastPaymentInfo(GoodsPurchaseEntity goodsPurchaseEntity)
        {
            string sql = SqlCommands.GetUpdateSql("goodspurchase", new string[] { "LastPaymentDate", "PaidLastAmount", "LatestPaidLastAmount" }, new string[] { "GoodsPurchaseId" });
            sql = string.Format("{0} {1} WHERE GoodsPurchaseId=?GoodsPurchaseId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseEntity.GoodsPurchaseId, DbType.Int64);
            parameters.Add("?LastPaymentDate", goodsPurchaseEntity.LastPaymentDate?.Date, DbType.DateTime);
            parameters.Add("?PaidLastAmount", goodsPurchaseEntity.PaidLastAmount, DbType.Decimal);
            parameters.Add("?LatestPaidLastAmount", goodsPurchaseEntity.LatestPaidLastAmount, DbType.Decimal);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<GoodsPurchaseEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改采购交付信息
        public async Task<GoodsPurchaseEntity> UpdateDeliveryInfo(GoodsPurchaseEntity goodsPurchaseEntity)
        {
            string sql = SqlCommands.GetUpdateSql("goodspurchase", new string[] { "DeliveryDate", "PurchaseContractNo", "PurchaseContractRemark", "InventoryStatus", "InventoryStatusStr", "HasNotifyPurchaseManage" }, new string[] { "GoodsPurchaseId" });
            sql = string.Format("{0} {1} WHERE GoodsPurchaseId=?GoodsPurchaseId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseEntity.GoodsPurchaseId, DbType.Int64);
            parameters.Add("?DeliveryDate", goodsPurchaseEntity.DeliveryDate.Value.Date, DbType.Date);
            parameters.Add("?PurchaseContractNo", goodsPurchaseEntity.PurchaseContractNo, DbType.AnsiString,size:50);
            parameters.Add("?PurchaseContractRemark", goodsPurchaseEntity.PurchaseContractRemark, DbType.String);
            parameters.Add("?HasNotifyPurchaseManage", true, DbType.Boolean);
            parameters.Add("?InventoryStatus", PurchaseInventoryStatusType.Transferring.ToString(), DbType.AnsiString, size: 30);
            parameters.Add("?InventoryStatusStr", PurchaseInventoryStatusType.Transferring.GetDisplayString(), DbType.AnsiString, size: 30);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<GoodsPurchaseEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改采购暂停备注信息
        public async Task<GoodsPurchaseEntity> UpdateSuspendRemark(GoodsPurchaseEntity goodsPurchaseEntity)
        {
            string sql = SqlCommands.GetUpdateSql("goodspurchase", new string[] { "SuspendTime", "SuspendRemark","PurchaseStatus", "PurchaseStatusStr" }, new string[] { "GoodsPurchaseId" });
            sql = string.Format("{0} {1} WHERE GoodsPurchaseId=?GoodsPurchaseId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseEntity.GoodsPurchaseId, DbType.Int64);
            parameters.Add("?SuspendTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?SuspendRemark", goodsPurchaseEntity.SuspendRemark, DbType.String);
            parameters.Add("?PurchaseStatus", PurchaseStatusType.Suspend.ToString() , DbType.String);
            parameters.Add("?PurchaseStatusStr", PurchaseStatusType.Suspend.GetDisplayString(), DbType.String);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<GoodsPurchaseEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改采购暂停恢复备注信息
        public async Task<GoodsPurchaseEntity> UpdateRestoreRemark(GoodsPurchaseEntity goodsPurchaseEntity)
        {
            string sql = SqlCommands.GetUpdateSql("goodspurchase", new string[] { "RestoreTime", "RestoreRemark", "PurchaseStatus", "PurchaseStatusStr" }, new string[] { "GoodsPurchaseId" });
            sql = string.Format("{0} {1} WHERE GoodsPurchaseId=?GoodsPurchaseId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseEntity.GoodsPurchaseId, DbType.Int64);
            parameters.Add("?RestoreTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?RestoreRemark", goodsPurchaseEntity.RestoreRemark, DbType.String);
            parameters.Add("?PurchaseStatus", PurchaseStatusType.Running.ToString(), DbType.String);
            parameters.Add("?PurchaseStatusStr", PurchaseStatusType.Running.GetDisplayString(), DbType.String);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<GoodsPurchaseEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改采购停止备注信息
        public async Task<GoodsPurchaseEntity> UpdateStopRemark(GoodsPurchaseEntity goodsPurchaseEntity)
        {
            string sql = SqlCommands.GetUpdateSql("goodspurchase", new string[] { "StopTime", "StopRemark", "PurchaseStatus", "PurchaseStatusStr" }, new string[] { "GoodsPurchaseId" });
            sql = string.Format("{0} {1} WHERE GoodsPurchaseId=?GoodsPurchaseId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseEntity.GoodsPurchaseId, DbType.Int64);
            parameters.Add("?StopTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?StopRemark", goodsPurchaseEntity.StopRemark, DbType.String);
            parameters.Add("?PurchaseStatus", PurchaseStatusType.Stop.ToString(), DbType.String);
            parameters.Add("?PurchaseStatusStr", PurchaseStatusType.Stop.GetDisplayString(), DbType.String);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<GoodsPurchaseEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改采购单定金凭证信息
        public async Task<GoodsPurchaseEntity> UpdateDepositeCertificate(GoodsPurchaseEntity goodsPurchaseEntity)
        {
            string sql = SqlCommands.GetUpdateSql("goodspurchase", new string[] { "DepositePaymentCertificateNo" }, new string[] { "GoodsPurchaseId" });
            sql = string.Format("{0} {1} WHERE GoodsPurchaseId=?GoodsPurchaseId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseEntity.GoodsPurchaseId, DbType.Int64);
            parameters.Add("?DepositePaymentCertificateNo", goodsPurchaseEntity.DepositePaymentCertificateNo, DbType.AnsiString);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<GoodsPurchaseEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 修改采购单尾款凭证信息
        public async Task<GoodsPurchaseEntity> UpdateLastPaymentCertificate(GoodsPurchaseEntity goodsPurchaseEntity)
        {
            string sql = SqlCommands.GetUpdateSql("goodspurchase", new string[] { "LastPaymentCertificateNo" }, new string[] { "GoodsPurchaseId" });
            sql = string.Format("{0} {1} WHERE GoodsPurchaseId=?GoodsPurchaseId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?GoodsPurchaseId", goodsPurchaseEntity.GoodsPurchaseId, DbType.Int64);
            parameters.Add("?DepositePaymentCertificateNo", goodsPurchaseEntity.DepositePaymentCertificateNo, DbType.AnsiString);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<GoodsPurchaseEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion
    }
}



